package UsersPackage;

import Util.DBUtil;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class UserDao {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Statement stmt = null;

    public Integer register(String username, String user_id, String password, String email,String birth,String phone,String address) {
        try {
            con = DBUtil.getConnection();
            stmt = con.createStatement();
            System.out.println("连接成功？");
            System.out.println("连接成功");
            String date = getCurDatetiment();

            if (isUserIdExists(user_id)) {
                System.out.println("User with user_id already exists.");
                return 0;
            }

            // Check if username is already registered
            if (isUsernameExists(username)) {
                System.out.println("User with username already exists.");
                return 0;
            }
            String sql = "insert into user(username, user_id, password, email, birth, phone, address, creationTime)value('"
                    + username + "','" + user_id + "','" + password + "','" + email + "','" + birth + "','" + phone + "','" +
                    address + "','" + date + "') ";
            Integer rtn = stmt.executeUpdate(sql);
            System.out.println(con);
            return rtn;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    public Integer updateUser(String username, String user_id, String password, String email, String birth, String phone, String address) {
        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "UPDATE user SET user_id=? ,password=?, email=?, birth=?, phone=?, address=? WHERE username=?";
            ps = con.prepareStatement(sql);

            ps.setString(1, user_id);
            ps.setString(2, password);
            ps.setString(3, email);
            ps.setString(4, birth);
            ps.setString(5, phone);
            ps.setString(6, address);
            ps.setString(7, username);

            // Execute the update
            int rowsUpdated = ps.executeUpdate();
            System.out.println(ps);
            System.out.println(username);
            return rowsUpdated;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    public int updateUserByUsername(String username, String user_id, String password, String email, String birth, String phone, String address,boolean userPower) {
        PreparedStatement ps = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "UPDATE user SET user_id=?, password=?, email=?, birth=?, phone=?, address=?, power=? WHERE username=?";
            ps = con.prepareStatement(sql);

            ps.setString(1, user_id);
            ps.setString(2, password);
            ps.setString(3, email);
            ps.setString(4, birth);
            ps.setString(5, phone);
            ps.setString(6, address);
            ps.setBoolean(7, userPower);
            ps.setString(8, username);

            // Execute the update
            int rowsUpdated = ps.executeUpdate();
            System.out.println(ps);
            System.out.println(username);

            return rowsUpdated;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    public int deleteUserByUsername(String username) {
        PreparedStatement ps = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "DELETE FROM user WHERE username=?";
            ps = con.prepareStatement(sql);

            ps.setString(1, username);

            // Execute the delete
            int rowsDeleted = ps.executeUpdate();
            System.out.println(ps);
            System.out.println(username);

            return rowsDeleted;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    public User queryUserByUsername(String username) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "SELECT * FROM user WHERE username=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);

            // Execute the query
            rs = ps.executeQuery();
            System.out.println(username);
            if (rs.next()) {
                // Create a User object with retrieved information
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setUser_id(rs.getString("user_id"));
                user.setPassword(rs.getString("password"));
                // Set other user information as needed
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null; // Return null if the user is not found or an error occurs
    }
    public User validateUser(String username, String password) {
        Connection conn;
        PreparedStatement stmt;
        ResultSet rs;
        User user = new User();
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT user_id, username, password, power FROM user WHERE username = ? AND password = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            rs = stmt.executeQuery();
            String user_id = rs.getString("user_id");
            user.setUser_id(user_id);
            boolean power = rs.getBoolean("power");
            user.setpower(power);
            return user;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean isUserIdExists(String user_id) throws SQLException {
        con = DBUtil.getConnection();
        stmt = con.createStatement();
        String query = "SELECT COUNT(*) FROM user WHERE user_id = '" + user_id + "'";
        try (Statement statement = con.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                return count > 0;
            }
        }
        return false;
    }

    public boolean isUsernameExists(String username) throws SQLException {
        con = DBUtil.getConnection();
        stmt = con.createStatement();
        String query = "SELECT COUNT(*) FROM user WHERE username = '" + username + "'";
        try (Statement statement = con.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                return count > 0;
            }
        }
        return false;
    }
    //生成注册日期
    private String getCurDatetiment() {
        // TODO 自动生成的方法存根
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return format.format(new Date());
    }
}
